<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class StatisticsAttended extends AdminController {

	function __construct() {
		parent::__construct();
		$this-> load->model('Attend_model');
	}

	function Index(){
		$this->load->view('admin/statisticsAttendedList');
	}

	function getDatas($params = array(), $isExport = false){
		$connar = array();
		if (!$isExport) {
			$params = $_POST;
			$connar = $this -> Attend_model -> pickPages($connar, $params);
		}
		// $connar['items']='db_attend.id,db_attend.dept_name';
		// $connar['where']=$this->sqlEscape('db_attend.status=??',array(AttendStatus::Submit[XPHEnum::Value]));
		// $connar['join']=array('db_admin','db_admin.id=db_attend.listen_id');
		// $connar['groupby']='db_attend.dept_name ';

		$where='where db_attend.status='.AttendStatus::Submit[XPHEnum::Value];
		if (isset($params['start_time']) && isset($params['end_time'])) {
			$where.=' and db_attend.listen_time>='.strtotime($params['start_time']).' and db_attend.listen_time<='.strtotime($params['end_time'].'23:59:59');
		}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
			$where.=' and db_attend.listen_time>='.strtotime($_POST['start_time']).' and db_attend.listen_time<='.strtotime($_POST['end_time'].'23:59:59');
		}
		if(!$isExport){
			if(isset($params['pageNo']) && isset($params['pageSize'])){			
				$pageNo=$_POST['pageNo']-1;
				$pageSize=$_POST['pageSize'];
			}else{
				$pageNo='0';
				$pageSize='20';
			}
		}
		if (!$isExport) {
			$data = $this -> Attend_model -> get_page_list($connar);
			$data['ret']=$this -> Attend_model -> query('select * from(select db_attend.id,db_attend.dept_name from db_attend left join db_admin on db_admin.id=db_attend.listen_id '.$where.') attend group by dept_name limit '.$pageSize*$pageNo.','.$pageSize);
			$count=$this -> Attend_model -> query('select count(*) count from(select * from(select db_attend.id,db_attend.dept_name from db_attend left join db_admin on db_admin.id=db_attend.listen_id '.$where.') attend group by dept_name)a limit '.$pageSize*$pageNo.','.$pageSize);
			$data['count']=$count[0]['count'];
			$data['totalPage']=ceil($data['count']/$pageSize);
		} else {
			$data['ret']=$this -> Attend_model -> query('select * from(select db_attend.id,db_attend.dept_name from db_attend left join db_admin on db_admin.id=db_attend.listen_id '.$where.') attend group by dept_name');
			$count=$this -> Attend_model -> query('select count(*) count from(select * from(select db_attend.id,db_attend.dept_name from db_attend left join db_admin on db_admin.id=db_attend.listen_id '.$where.') attend group by dept_name)a');
		};
		foreach ($data['ret'] as $k => $v) {
			// 被听人数
			$connar1['items']='count(DISTINCT teacher_name) attend_num';
			$connar1['where']=$this->sqlEscape('dept_name=?? and status=??',array($v['dept_name'],AttendStatus::Submit[XPHEnum::Value]));
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}
			$num=$this->Attend_model->get_single_full($connar1);
			$data['ret'][$k]['attend_num']=$num['attend_num'];
			// 被听人次
			$connar1['items']='COUNT(*) attend_count';
			$connar1['where']=$this->sqlEscape('dept_name=?? and status=??',array($v['dept_name'],AttendStatus::Submit[XPHEnum::Value]));
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}
			$count=$this->Attend_model->get_single_full($connar1);
			$data['ret'][$k]['attend_count']=$count['attend_count'];
			// 分院督导听课人数	--本分院
			$connar2['items']='COUNT(DISTINCT db_admin.id) unitsupervisor_num';
			$connar2['where']=$this->sqlEscape('db_admin.dept_name=db_attend.dept_name and db_attend.dept_name=?? and type=?? and db_attend.status=??',array($v['dept_name'],AttendType::Unit[XPHEnum::Value],AttendStatus::Submit[XPHEnum::Value]));	
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar2['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar2['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}
			$connar2['join']=array('db_admin','db_admin.id=db_attend.listen_id');
			$unitsupervisor_num=$this->Attend_model->get_single_full($connar2);
			$data['ret'][$k]['unitsupervisor_num']=$unitsupervisor_num['unitsupervisor_num'];
			// 分院督导听课次数	
			$connar2['items']='COUNT(*) unitsupervisor_count';
			$connar2['where']=$this->sqlEscape('db_admin.dept_name=db_attend.dept_name and db_attend.dept_name=?? and type=?? and db_attend.status=??',array($v['dept_name'],AttendType::Unit[XPHEnum::Value],AttendStatus::Submit[XPHEnum::Value]));
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar2['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar2['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}
			$connar2['join']=array('db_admin','db_admin.id=db_attend.listen_id');
			$unitsupervisor_count=$this->Attend_model->get_single_full($connar2);
			$data['ret'][$k]['unitsupervisor_count']=$unitsupervisor_count['unitsupervisor_count'];
			// 校督导听课人数	
			$connar2['items']='COUNT(DISTINCT db_admin.id) schoolsupervisor_num';
			$connar2['where']=$this->sqlEscape('type=?? and db_attend.dept_name=?? and db_attend.status=??',array(AttendType::School[XPHEnum::Value],$v['dept_name'],AttendStatus::Submit[XPHEnum::Value]));
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar2['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar2['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}
			$connar2['join']=array('db_admin','db_admin.id=db_attend.listen_id');
			$schoolsupervisor_num=$this->Attend_model->get_single_full($connar2);
			$data['ret'][$k]['schoolsupervisor_num']=$schoolsupervisor_num['schoolsupervisor_num'];
			// 校督导听课次数	
			$connar2['items']='COUNT(*) schoolsupervisor_count';
			$connar2['where']=$this->sqlEscape('type=?? and db_attend.dept_name=?? and db_attend.status=??',array(AttendType::School[XPHEnum::Value],$v['dept_name'],AttendStatus::Submit[XPHEnum::Value]));
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar2['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar2['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}
			$connar2['join']=array('db_admin','db_admin.id=db_attend.listen_id');
			$schoolsupervisor_count=$this->Attend_model->get_single_full($connar2);
			$data['ret'][$k]['schoolsupervisor_count']=$schoolsupervisor_count['schoolsupervisor_count'];
			// 平均分			
			$connar1['items']='(sum(teaching_level_score)+sum(teaching_effect_score))/'.(intval($count['attend_count'])*2).' avg';
			$connar1['where']=$this->sqlEscape('dept_name=?? and status=??',array($v['dept_name'],AttendStatus::Submit[XPHEnum::Value]));
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}
			$avg=$this->Attend_model->get_single_full($connar1);
			$data['ret'][$k]['avg']=sprintf("%.2f",$avg['avg']);
			// 90分(含)以上,80以下
			$connar1['items']='id';
			$connar1['where']=$this->sqlEscape('dept_name=?? and status=??',array($v['dept_name'],AttendStatus::Submit[XPHEnum::Value]));
			$countnum=$this->Attend_model->get_list_full($connar1);
			$abovenine=0;
			$aboveeight=0;
			$maxid=0;
			$max=-1;
			$minid=0;
			$min=999;
			$max_all=-1;
			$min_all=999;
			foreach ($countnum as $k1 => $v1) {
				$connar1['items']='(sum(teaching_level_score)+sum(teaching_effect_score))/2 avg';
				$connar1['where']=$this->sqlEscape('id=?? and status=??',array($v1['id'],AttendStatus::Submit[XPHEnum::Value]));
				if (isset($params['start_time']) && isset($params['end_time'])) {
					$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
				}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
					$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
				}
				$countnumData[$k1]=$this->Attend_model->get_single_full($connar1);	
				if($countnumData[$k1]['avg']>=90){
					$abovenine++;
				}	
				// $countnumData=$this->Attend_model->get_single_full($connar1);	
				if($countnumData[$k1]['avg']<80){
					$aboveeight++;
				}
				if($max<$countnumData[$k1]['avg'] && $countnumData[$k1]['avg']!=null){
					$maxid=$v1['id'];
					$max=$countnumData[$k1]['avg'];
					if($max>$max_all){
						$max_all=$max;
						$maxid_all=$v1['id'];
					}
				}
				if($min>$countnumData[$k1]['avg'] && $countnumData[$k1]['avg']!=null){
					$minid=$v1['id'];
					$min=$countnumData[$k1]['avg'];
					if($min<$min_all){
						$min_all=$min;
						$minid_all=$v1['id'];
					}
				}
			}
			foreach ($countnum as $k1 => $v1) {
				// foreach ($countnumData as $k2 => $v2) {
					if($countnumData[$k1]['avg']==$max ){
						$maxid.=','.$v1['id'];
						if($max>$max_all){
							$maxid_all.=','.$v1['id'];
						}
					}
					if($countnumData[$k1]['avg']==$min){
						$minid.=','.$v1['id'];
						if($min<$min_all){
							$minid_all.=','.$v1['id'];
						}
					}
				// }
			}

			$data['ret'][$k]['abovenine']=$abovenine;
			$data['ret'][$k]['aboveeight']=$aboveeight;
			// 最高分
			$maxid_val=explode(',',$maxid);
			$connar1['items']='teacher_name';
			$connar1['where']=$this->sqlEscape('status=?? and (',array(AttendStatus::Submit[XPHEnum::Value]));
			$maxid_count=0;
			foreach ($maxid_val as $k1 => $v1) {
				$maxid_count++;
				if($maxid_count==1){
					$connar1['where'].=$this->sqlEscape(' id=?? ',array($v1));
				}else{
					$connar1['where'].=$this->sqlEscape(' or id=?? ',array($v1));
				}
				if($maxid_count==count($maxid_val) ){
					$connar1['where'].=')';
				}				
			}
			// if (isset($params['start_time']) && isset($params['end_time'])) {
			// 	$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
			// }else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
			// 	$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
			// }
			$maxnum=$this->Attend_model->get_list_full($connar1);
			$maxname='';
			$maxname_count=0;
			foreach ($maxnum as $k1 => $v1) {
				$maxname_count++;
				if($maxname_count==1){
					$maxname.=$v1['teacher_name'];
				}else{
					if(!strstr($maxname,$v1['teacher_name'])){
					   $maxname.='、'.$v1['teacher_name'];
					}
				}
			}
			$data['ret'][$k]['max']=sprintf("%.2f",$max).'('.$maxname.')';
			
			// 最低分
			$minid_val=explode(',',$minid);
			$connar1['items']='teacher_name';
			$connar1['where']=$this->sqlEscape('status=?? and (',array(AttendStatus::Submit[XPHEnum::Value]));
			$minid_count=0;
			foreach ($minid_val as $k1 => $v1) {
				$minid_count++;
				if($minid_count==1){
					$connar1['where'].=$this->sqlEscape(' id=?? ',array($v1));
				}else{
					$connar1['where'].=$this->sqlEscape(' or id=?? ',array($v1));
				}
				if($minid_count==count($minid_val)){
					$connar1['where'].=')';
				}				
			}
			// if (isset($params['start_time']) && isset($params['end_time'])) {
			// 	$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
			// }else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
			// 	$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
			// }
			$minnum=$this->Attend_model->get_list_full($connar1);
			$minname='';
			$minname_count=0;
			foreach ($minnum as $k1 => $v1) {
				$minname_count++;
				if($minname_count==1){
					$minname.=$v1['teacher_name'];
				}else{
					if(!strstr($minname,$v1['teacher_name'])){
					    $minname.='、'.$v1['teacher_name'];
					}
				}
			}
			$data['ret'][$k]['min']=sprintf("%.2f",$min).'('.$minname.')';
			
		}
		
		if (!$isExport) {
			echo json_encode($data);
		} else {
			// return $data["ret"];
			$attend_num_count=0;
			$attend_count_count=0;
			$unitsupervisor_num_count=0;
			$unitsupervisor_count_count=0;
			$schoolsupervisor_num_count=0;
			$schoolsupervisor_count_count=0;
			$avg_count=0;
			$abovenine_count=0;
			$aboveeight_count=0;
			$max_count=0;
			$min_count=0;
			// $connar1['items']='teacher_name';
			// $connar1['where']=$this->sqlEscape('id=?? and status=??',array($maxid_all,AttendStatus::Submit[XPHEnum::Value]));
			// $max_all_name=$this->Attend_model->get_single_full($connar1);
			$max_all_name=sprintf("%.2f",$max).'('.$maxname.')';
			// $connar1['items']='teacher_name';
			// $connar1['where']=$this->sqlEscape('id=?? and status=??',array($minid_all,AttendStatus::Submit[XPHEnum::Value]));
			// $min_all_name=$this->Attend_model->get_single_full($connar1);
			$min_all_name=sprintf("%.2f",$min).'('.$minname.')';
			foreach ($data['ret'] as $k => $v) {
				$attend_num_count+=$v['attend_num'];
				$attend_count_count+=$v['attend_count'];
				$unitsupervisor_num_count+=$v['unitsupervisor_num'];
				$unitsupervisor_count_count+=$v['unitsupervisor_count'];
				$schoolsupervisor_num_count+=$v['schoolsupervisor_num'];
				$schoolsupervisor_count_count+=$v['schoolsupervisor_count'];
				$avg_count+=$v['avg'];
				$abovenine_count+=$v['abovenine'];
				$aboveeight_count+=$v['aboveeight'];
				$max_count+=$v['max'];
				$min_count+=$v['min'];
			}			
			$avg_count=sprintf("%.2f",($avg_count)/count($data['ret']));
			array_push($data['ret'],array('title'=>'合计','attend_num_count'=>$attend_num_count,'attend_count_count'=>$attend_count_count,'unitsupervisor_num_count'=>$unitsupervisor_num_count,'unitsupervisor_count_count'=>$unitsupervisor_count_count,'schoolsupervisor_num_count'=>$schoolsupervisor_num_count,'schoolsupervisor_count_count'=>$schoolsupervisor_count_count,'avg_count'=>$avg_count,'abovenine_count'=>$abovenine_count,'aboveeight_count'=>$aboveeight_count,'max_count'=>$max_all_name,'min_count'=>$min_all_name));
			$this-> load ->library('PHPExcel/PHPExcel');
	        $excel = new PHPExcel();
	        //公共样式
	        $excel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(11); 
	        $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	        $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setWrapText(true);
	        $excel->getActiveSheet()->getDefaultColumnDimension()->setWidth(23);
	        $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	        $excel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);

	        $x_line=0;
	        $y_line=1;

			//标题
	        $title=array($_POST['start_time'].'至'.$_POST['end_time'],'被听人数','被听人次','分院督导听课人数','分院督导听课次数','校督导听课人数','校督导听课次数','平均分','90分(含)以上','80分以下','最高分','最低分');
	        foreach ($title as $k => $v) {
	        	$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line).$y_line,$v);
	        	$excel->getActiveSheet()->getStyle(PHPExcel_Cell::stringFromColumnIndex($x_line).$y_line)->getFont()->setBold(true);   
	        	$x_line++;
	        }

	        $x_line=0;
	        $y_line=2;
	        //正式数据
	        
	        foreach ($data['ret'] as $k => $v) {        	
	        	foreach ($v as $k1 => $v1) {
	        		// if($k1=='id'){
	        		// 	$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line).$y_line,$_POST['start_time'].'至'.$_POST['end_time']);
	        		// }else{
	        		if($k1=='id'){
	        			continue;
	        		}
	        			$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line).$y_line,$v1);
	        		// }
	        		// }
	        		
	        		$x_line++;
	        	}
	        	$x_line=0;
	        	$y_line++;
	        }
	        $write = new PHPExcel_Writer_Excel2007($excel); 
	       	$director = "upload/export/" . date('Ymd', time()) . "/";
	        if (!file_exists($director)) {
	            //检查是否有该文件夹，如果没有就创建，并给予最高权限
	            mkdir($director, 0777);
	        }        
			$file_name ='听课数据分析表'.date('Ymd').'.xlsx';       	
	       	$write->save($director.iconv('utf-8', 'gb2312', $file_name));
			exit(retJson('返回地址',true,$director.$file_name));
		}
	}
	
}



